using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data;
using Fieldbook.Entities;
using Fieldbook.Common;

namespace Fieldbook.DAL
{
    public static class FieldWorkDAO
    {
        #region select
        private static List<FieldWork> LoadFieldWorks(SqlDatabase db, DbCommand command)
        {
            List<FieldWork> res = new List<FieldWork>();
            IDataReader reader = null;
            try
            {
                reader = db.ExecuteReader(command);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        FieldWork entity = new FieldWork();
                        entity.FieldWorkID = reader.GetInt64(0);
                        entity.FieldID = reader.GetInt64(1);
                        entity.ExecutorID = reader.GetInt64(2);
                        entity.LoggerID = reader.GetInt64(3);
                        entity.ExecutionDate = reader.GetDateTime(4);
                        entity.LoggedDate = reader.GetDateTime(5);
                        entity.TotalCost = reader.GetDecimal(6);
                        entity.Remarks = reader.IsDBNull(7) ? null : reader.GetString(7);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<FieldWork> LoadFieldWorks(long fieldID)
        {
            string selectSQL = @"SELECT FIELDWORKID, FIELDID, EXECUTORID, LOGGERID, EXECUTIONDATE, LOGGEDDATE, TOTALCOST, REMARKS FROM dbo.FieldWork WHERE FIELDID = @FIELDID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@FIELDID", DbType.Int64, fieldID);
            return LoadFieldWorks(db, cmd);
        }

        public static List<FieldWork> LoadFieldWorks(long fieldID, string orderby, int maximumRows, int startRowIndex)
        {
            if (String.IsNullOrEmpty(orderby))
            {
                orderby = "FIELDWORKID";
            }
            int endRowIndex = startRowIndex + maximumRows - 1;
            string selectSQL = String.Format(@"WITH FIELDWORKS AS (
                SELECT ROW_NUMBER() OVER (ORDER BY {0} ASC) AS ROW, FIELDWORKID, FIELDID, EXECUTORID, LOGGERID, EXECUTIONDATE, LOGGEDDATE, TOTALCOST, REMARKS FROM dbo.FieldWork WHERE FIELDID = @FIELDID
                                                     )
                SELECT FIELDWORKID, FIELDID, EXECUTORID, LOGGERID, EXECUTIONDATE, LOGGEDDATE, TOTALCOST, REMARKS FROM FIELDWORKS
                        WHERE ROW BETWEEN {1} AND {2}", orderby, startRowIndex, endRowIndex);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@FIELDID", DbType.Int64, fieldID);
            return LoadFieldWorks(db, cmd);
        }

        public static int GetFieldWorkCount(long fieldID)
        {
            string selectSQL = @"SELECT COUNT(FIELDWORKID)AS fw_count FROM dbo.FieldWork WHERE FIELDID = @FIELDID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@FIELDID", DbType.Int64, fieldID);
            int res = (int)db.ExecuteScalar(cmd);
            return res;
        }

        #endregion

        #region Insert
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSQL = @"INSERT INTO dbo.FieldWork (FIELDID,EXECUTORID,LOGGERID,EXECUTIONDATE,LOGGEDDATE,TOTALCOST,REMARKS)
				VALUES (@FIELDID, @EXECUTORID, @LOGGERID, @EXECUTIONDATE, @LOGGEDDATE, @TOTALCOST, @REMARKS)
                SET @FIELDWORKID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@FIELDWORKID", DbType.Int64, 8);
            db.AddInParameter(insertCommand, "@FIELDID", DbType.Int64);
            db.AddInParameter(insertCommand, "@EXECUTORID", DbType.Int64);
            db.AddInParameter(insertCommand, "@LOGGERID", DbType.Int64);
            db.AddInParameter(insertCommand, "@EXECUTIONDATE", DbType.DateTime);
            db.AddInParameter(insertCommand, "@LOGGEDDATE", DbType.DateTime);
            db.AddInParameter(insertCommand, "@TOTALCOST", DbType.Decimal);
            db.AddInParameter(insertCommand, "@REMARKS", DbType.String);
            return insertCommand;
        }
        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, FieldWork entity)
        {
            db.SetParameterValue(cmd, "@FIELDID", entity.FieldID);
            db.SetParameterValue(cmd, "@EXECUTORID", entity.ExecutorID);
            db.SetParameterValue(cmd, "@LOGGERID", entity.LoggerID);
            db.SetParameterValue(cmd, "@EXECUTIONDATE", entity.ExecutionDate);
            db.SetParameterValue(cmd, "@LOGGEDDATE", entity.LoggedDate);
            db.SetParameterValue(cmd, "@TOTALCOST", entity.TotalCost);
            db.SetParameterValue(cmd, "@REMARKS", entity.Remarks);
        }
        public static bool InsertFieldWork(FieldWork entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.FieldWorkID = (long?)db.GetParameterValue(cmd, "@FIELDWORKID");
            }
            return res;
        }
        public static bool InsertFieldWork(FieldWork entity, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = false;
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            if (res)
            {
                entity.FieldWorkID = (long?)db.GetParameterValue(cmd, "@FIELDWORKID");
            }
            return res;
        }
        #endregion

        #region Delete
        private static DbCommand GetDeleteCommand(SqlDatabase db)
        {
            string deleteSQL = "DELETE FROM dbo.FieldWork WHERE FIELDWORKID = @FIELDWORKID";
            DbCommand deleteCommand = db.GetSqlStringCommand(deleteSQL);
            db.AddInParameter(deleteCommand, "@FIELDWORKID", DbType.Int64);
            return deleteCommand;
        }
        private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, FieldWork entity)
        {
            db.SetParameterValue(cmd, "@FIELDWORKID", entity.FieldWorkID);
        }
        public static bool DeleteFieldWork(FieldWork entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetDeleteCommand(db);
            SetDeleteValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
        #endregion
    }
}
